package vn.edu.saigontech.ncms.web.receipt;

import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;

import vn.edu.saigontech.ncms.model.info.InternalReceipt;
import vn.edu.saigontech.ncms.model.info.Receipt;
import vn.edu.saigontech.ncms.model.program.Program;
import vn.edu.saigontech.ncms.model.system.Parameter;
import vn.edu.saigontech.ncms.service.InternalReceiptService;
import vn.edu.saigontech.ncms.service.ParameterService;
import vn.edu.saigontech.ncms.service.ReceiptService;
import vn.edu.saigontech.ncms.util.DateUtil;
import vn.edu.saigontech.ncms.util.ExcelUtil;
import vn.edu.saigontech.ncms.web.BaseAction;

@SuppressWarnings("serial")
public class ReceiptReportAction extends BaseAction {
	private int month;
	private int year;
	private String date;
	
	public String showDailyReport() {
		this.date = DateUtil.toString(DateUtil.newDate(), this.i18n("format.date"));
		return SUCCESS;
	}
	
	public String reportDaily() {
		Date dateDate = DateUtil.fromString(this.date, this.i18n("format.date"));
		int day = DateUtil.getField(dateDate, Calendar.DAY_OF_MONTH);
		int month = DateUtil.getField(dateDate, Calendar.MONTH);
		int year = DateUtil.getField(dateDate, Calendar.YEAR);
		
		List<Receipt> receipts = ReceiptService.INSTANCE.findByDateAsc(dateDate, DateUtil.endOfDate(dateDate));
		List<InternalReceipt> ireceipts = InternalReceiptService.INSTANCE.findByDateAsc(dateDate, dateDate);
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Receipt Report - " + (month + 1) + ".xls\"");
		
		try {
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Report");
			
			HSSFFont fontBold16 = ExcelUtil.makeFont(workbook, "Times New Roman", 16, true);
			HSSFFont fontBold12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, true);
			HSSFFont fontBold11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, true);
			HSSFFont fontBold10 = ExcelUtil.makeFont(workbook, "Times New Roman", 10, true);
			
			HSSFFont fontNormal12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, false);
			HSSFFont fontNormal11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, false);
			
			HSSFCellStyle styleCenter_B16 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold16);
			HSSFCellStyle styleCenter_B12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold12);
			HSSFCellStyle styleCenter_B11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold11);
			HSSFCellStyle styleCenter_B10 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold10);
			
			HSSFCellStyle styleCenter_N12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal12);
			HSSFCellStyle styleCenter_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			HSSFCellStyle styleLeft_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			HSSFCellStyle styleRight_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			
			int r = 0;
			HSSFRow row = sheet.createRow(r);
			
			HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B16);
			row.setHeightInPoints(21);
			cell.setCellValue(new HSSFRichTextString("BẢNG KÊ CHI TIẾT NỘP TIỀN HỌC PHÍ ANH VĂN THÁNG " + (month + 1) + "/" + year));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell.setCellValue(new HSSFRichTextString("Cơ Sở: Quang Trung"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 10));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(new HSSFRichTextString("Ngày " + DateUtil.toString(dateDate, "dd/MM/yyyy")));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 10));
			
			// BLANK
			r = r + 1;
			row = sheet.createRow(r);
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(18);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, "STT", 256*5, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "NGÀY", 256*11, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "SỐ BIÊN LAI", 256*16, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "HỌ TÊN", 256*30, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "KHÓA HỌC", 256*13, styleCenter_B11);
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 1, 1));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 2, 2));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 3, 3));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 4, 4));
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 9, "GHI CHÚ", 256*38, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 10, "NGƯỜI THU", 256*12, styleCenter_B11);
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 9, 9));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 10, 10));
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "SỐ TIỀN", 256*11, styleCenter_B11);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 8));
			
			r = r + 1;
			row = sheet.createRow(r);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "VND", 256*11, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "USD", 256*6, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "HOÀN LẠI (VND)", 256*13, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 8, "TC (VND)", 256*11, styleCenter_B11);
			
			int firstRow = r + 2;
			int index = 1;
			for(Receipt receipt : receipts) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(18);
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(index);

				Date date = receipt.getDate();
				String value = DateUtil.getDayOfMonth(date) + "-" + DateUtil.getMonthAsString(date) + "-" + new String(year + "").substring(2, 4);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(value));
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(receipt.getNumber()));
				
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleLeft_N11);
				cell.setCellValue(new HSSFRichTextString(receipt.getStudent().getFullName()));
				
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(receipt.getOffering().getName()));
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(receipt.getFee().getAmount());
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				
				cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleRight_N11);
				cell.setCellValue(receipt.getFee().getAmount());
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				
				cell = row.createCell(9, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleLeft_N11);
				cell.setCellValue(new HSSFRichTextString(receipt.getReceiptComment()));
				
				cell = row.createCell(10, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(receipt.getCashier().getFirstName()));
				
				index++;
			}
			/* START -- report for internal receipt */
			for(InternalReceipt ireceipt : ireceipts) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(18);
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(index);

				Date date = ireceipt.getDate();
				String value = DateUtil.getDayOfMonth(date) + "-" + DateUtil.getMonthAsString(date) + "-" + new String(year + "").substring(2, 4);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(value));
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(ireceipt.getNumber()));
				
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleLeft_N11);
				cell.setCellValue(new HSSFRichTextString(ireceipt.getName()));
				
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue("");
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(ireceipt.getFee().getAmount());
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				
				cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleRight_N11);
				cell.setCellValue(ireceipt.getFee().getAmount());
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				
				cell = row.createCell(9, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleLeft_N11);
				cell.setCellValue(new HSSFRichTextString(ireceipt.getReceiptComment()));
				
				cell = row.createCell(10, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(ireceipt.getCashier().getFirstName()));
				
				index++;
			}
			/* END -- report for internal receipt */
			
			int lastRow = r + 1;
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(18);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B10);
			cell.setCellValue(new HSSFRichTextString("TỔNG CỘNG"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 4));
			
			cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B10);
			if(lastRow >= firstRow) {
				cell.setCellFormula("SUM(F" + firstRow + ":"  + "F" + lastRow + ")");
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			}
			
			cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B10);
			if(lastRow >= firstRow) {
				cell.setCellFormula("+SUM(I" + firstRow + ":"  + "I" + lastRow + ")");
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			}
			
			r = r + 2;
			row = sheet.createRow(r);
			row.setHeightInPoints(17);
			
			cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(new HSSFRichTextString("Tp.Hồ Chí Minh, ngày " + day + " tháng " + (month + 1) + " năm " + year));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 10));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(17);
			
			cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(new HSSFRichTextString("Người nộp"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 10));
			
			r = r + 5;
			row = sheet.createRow(r);
			row.setHeightInPoints(17);
			
			cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell.setCellValue(new HSSFRichTextString("Lê Thị Thanh Nhung"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 10));
			
			workbook.write(RESPONSE().getOutputStream());
		} catch(Exception ex) {
			ex.printStackTrace();
		}
		return NONE;
	}
	
	public String report() {
		Date fromDate = DateUtil.getStartDateOfMonth(month, year);
		Date toDate = DateUtil.getEndDateOfMonth(month, year);
		
		List<Receipt> receipts = ReceiptService.INSTANCE.findByDateAsc(fromDate, DateUtil.endOfDate(toDate));
		List<InternalReceipt> ireceipts = InternalReceiptService.INSTANCE.findByDateAsc(fromDate, toDate);
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Receipt Report - " + (this.month) + ".xls\"");
		
		try {
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Report");
			
			HSSFFont fontBold16 = ExcelUtil.makeFont(workbook, "Times New Roman", 16, true);
			HSSFFont fontBold10 = ExcelUtil.makeFont(workbook, "Times New Roman", 10, true);
			HSSFFont fontBold11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, true);
			HSSFFont fontBold9 = ExcelUtil.makeFont(workbook, "Times New Roman", 9, true);
			
			HSSFFont fontNormal11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, false);
			
			HSSFCellStyle styleCenter_B16 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold16);
			HSSFCellStyle styleCenter_B11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold11);
			HSSFCellStyle styleCenter_B10 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold10);
			HSSFCellStyle styleCenter_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			HSSFCellStyle styleLeft_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			HSSFCellStyle styleRight_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			HSSFCellStyle styleRight_B11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_CENTER, fontBold11);

			HSSFCellStyle styleCenter_B9_BG = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold9);
			styleCenter_B9_BG.setFillForegroundColor(new HSSFColor.GREEN().getIndex());
			
			int r = 0;
			HSSFRow row = sheet.createRow(r);
			
			HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B16);
			row.setHeightInPoints(25);
			cell.setCellValue(new HSSFRichTextString("BẢNG KÊ THU HỌC PHÍ CHƯƠNG TRÌNH ANH NGỮ QUỐC TẾ - THÁNG " + this.month + "/" + year));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(25);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B16);
			cell.setCellValue(new HSSFRichTextString("CƠ SỞ QUANG TRUNG"));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 13));
			
			// BLANK
			r = r + 1;
			row = sheet.createRow(r);
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(22);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, "STT", 256*5, styleCenter_B9_BG);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "NGÀY", 256*11, styleCenter_B9_BG);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "KHUYẾN HỌC / BỔ SUNG", 256*16, styleCenter_B9_BG);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "THIẾU NHI", 256*13, styleCenter_B9_BG);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "THIẾU NIÊN", 256*13, styleCenter_B9_BG);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "TỔNG QUÁT", 256*15, styleCenter_B9_BG);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "HỌ TÊN", 256*30, styleCenter_B9_BG);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "KHÓA HỌC", 256*13, styleCenter_B9_BG);
			
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 1, 1));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 2, 2));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 3, 3));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 4, 4));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 5, 5));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 6, 6));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 7, 7));
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 12, "MIỄN GIẢM", 256*38, styleCenter_B9_BG);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 13, "NGƯỜI THU", 256*12, styleCenter_B9_BG);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 14, "ĐỊA CHỈ", 256*30, styleCenter_B9_BG);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 15, "THỜI GIAN", 256*12, styleCenter_B9_BG);
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 12, 12));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 13, 13));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 14, 14));
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 8, "HỌC PHÍ", 256*11, styleCenter_B9_BG);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 8, 11));
			
			r = r + 1;
			row = sheet.createRow(r);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 8, "VND", 256*11, styleCenter_B9_BG);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 9, "USD", 256*6, styleCenter_B9_BG);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 10, "HOÀN LẠI (VND)", 256*13, styleCenter_B9_BG);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 11, "TC (VND)", 256*11, styleCenter_B9_BG);
			
			int firstRow = r + 2;
			
			int index = 1;
			for(Receipt receipt : receipts) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(18);
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(index);

				Date date = receipt.getDate();
				String value = DateUtil.getDayOfMonth(date) + "-" + DateUtil.getMonthAsString(date) + "-" + new String(year + "").substring(2, 4);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(value));
				
				int type = getType(receipt);
				if(type != -1) {
					int colIndex = type + 2;
					cell = row.createCell(colIndex, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(styleCenter_N11);
					cell.setCellValue(new HSSFRichTextString(receipt.getNumber()));
				}
				
				cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleLeft_N11);
				cell.setCellValue(new HSSFRichTextString(receipt.getStudent().getFullName()));
				
				cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(receipt.getOffering().getName()));
				
				cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(receipt.getFee().getAmount());
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				
				cell = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleRight_N11);
				cell.setCellValue(receipt.getFee().getAmount());
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				
				cell = row.createCell(12, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleLeft_N11);
				cell.setCellValue(new HSSFRichTextString(receipt.getReceiptComment()));
				
				cell = row.createCell(13, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(receipt.getCashier().getFirstName()));
				
				cell = row.createCell(14, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleLeft_N11);
				cell.setCellValue(new HSSFRichTextString(receipt.getStudent().getAddress()));
				
				int hour = DateUtil.getHour(date);
				int mins = DateUtil.getMinute(date);
				String time = (hour < 10 ? "0" + hour : hour) + ":" + (mins < 10 ? "0" + mins : mins);
				
				cell = row.createCell(15, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(time));
				
				index++;
			}
			/* START -- report for internal receipt */
			for(InternalReceipt ireceipt : ireceipts) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(18);
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(index);

				Date date = ireceipt.getDate();
				String value = DateUtil.getDayOfMonth(date) + "-" + DateUtil.getMonthAsString(date) + "-" + new String(year + "").substring(2, 4);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(value));
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(ireceipt.getNumber()));
				
				cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleLeft_N11);
				cell.setCellValue(new HSSFRichTextString(ireceipt.getName()));
				
				cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue("");
				
				cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(ireceipt.getFee().getAmount());
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				
				cell = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleRight_N11);
				cell.setCellValue(ireceipt.getFee().getAmount());
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				
				cell = row.createCell(12, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleLeft_N11);
				cell.setCellValue(new HSSFRichTextString(ireceipt.getReceiptComment()));
				
				cell = row.createCell(13, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(ireceipt.getCashier().getFirstName()));
				
				index++;
			}
			/* START -- report for internal receipt */
			int lastRow = r + 1;
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(18);
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(27);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B10);
			cell.setCellValue(new HSSFRichTextString("TỔNG CỘNG"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 7));
			
			cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			if(lastRow > firstRow) {
				cell.setCellFormula("+SUM(I" + firstRow + ":"  + "I" + lastRow + ")");
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			}
			
			cell = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleRight_B11);
			if(lastRow > firstRow) {
				cell.setCellFormula("+SUM(L" + firstRow + ":"  + "L" + lastRow + ")");
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			}
			
			r = r + 2;
			row = sheet.createRow(r);
			row.setHeightInPoints(15);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N11);
			cell.setCellValue(new HSSFRichTextString("Trưởng bộ phận"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 2));
			
			cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N11);
			cell.setCellValue(new HSSFRichTextString("Nhân viên"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 7));
			
			r = r + 5;
			row = sheet.createRow(r);
			row.setHeightInPoints(15);
			
			String lep = ParameterService.INSTANCE.findText(Parameter.LEP);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N11);
			cell.setCellValue(new HSSFRichTextString(lep));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 2));
			
			String rpt = ParameterService.INSTANCE.findText(Parameter.RPT);
			cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N11);
			cell.setCellValue(new HSSFRichTextString(rpt));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 7));
			
			workbook.write(RESPONSE().getOutputStream());
		} catch(Exception ex) {
			ex.printStackTrace();
		}
		return NONE;
	}
	
	/**
	 * return the receipt type correspond to excel export file
	 * 
	 * @param receipt
	 * @return 2: free, 3: youth, 4: teenager, 5: ge
	 */
	public int getType(Receipt receipt) {
		Program program = receipt.getOffering().getLevel().getProgram();
		if (receipt.getFee().getAmount().equals(0d) || receipt.getNumber().contains("HB")) {
			return 0;
		} else
			return program.getReportOrder();
	}
	
	public int getMonth() {
		return month;
	}

	public void setMonth(int month) {
		this.month = month;
	}

	public int getYear() {
		return year;
	}

	public void setYear(int year) {
		this.year = year;
	}

	public String getDate() {
		return date;
	}

	public void setDate(String date) {
		this.date = date;
	}
}
